package org.fan;


import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.exception.MYPOIException;


public class ExcelToTextTools {

	/**
	 * @param args
	 * @throws MYPOIException 
	 * @throws IOException 
	 */
	public static void main(String[] args) throws  MYPOIException {
		//1. 获取配置参数
		PropertiesVO propertiesVO = initConfigurations(true);
		String str ;
		String excelFilePath = propertiesVO.getExcelFileName();
		String txtFilePath = propertiesVO.getToTxtFileStartName();
		String midstr;
		//读取工作表配置，需要作为输入参数，可以考虑从args中获取
		int sheetflag = propertiesVO.getSheetflag();
		//2。 读取模板数据
		try {
			str = getStrFromFile(propertiesVO.getTxtTemplateFileName());
		} catch (IOException e1) {
			str = propertiesVO.getTxtTemplateStr();
		}
		//解析模板数据
		int longth = str.split("=").length;
		String[] strs = str.split("=");
		String[] paramIndexs = new String[longth];
		for(int i=0;i<longth;i++){
		   midstr = strs[i];
		   if(midstr.contains(",")){
			   paramIndexs[i]=midstr.substring(midstr.indexOf(",")+1).trim();
		   }else{
			   if(i==0){
				   paramIndexs[i]=midstr;
			   }
		   } 
		}
		txtFilePath = propertiesVO.getToTxtFileStartName();
		//写数据
		writeFile(getDataFromExcel(excelFilePath,paramIndexs,sheetflag),txtFilePath);
		
	}
	/**
	 * 读取excel文件的填入的数据
	 * @param excelFilePath
	 * @param paraIndexs
	 * @param sheetFlag
	 * @return
	 * @throws MYPOIException 
	 */
	private static List<StringBuffer> getDataFromExcel(String excelFilePath,String[] paraIndexs,int sheetFlag) throws MYPOIException {
		//从excel里读取数据源头
		List<ExcelDataVO> dataStrList = new ArrayList<ExcelDataVO>();
		ExcelDataVO excelDataVO = new ExcelDataVO();
		HSSFWorkbook workbook = new HSSFWorkbook();
		String clumnName;
		List<Object>  columnValueArray;
		HSSFSheet sheet = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		List<StringBuffer> sbResultArray = new ArrayList<StringBuffer>();
		File uploadFile = new File(excelFilePath);
		try {
			FileInputStream fileInputStream = new FileInputStream(uploadFile);
			workbook = new HSSFWorkbook(fileInputStream);
		} catch (Exception e) {
			String exceptonMSG = "读取文件发生异常,文件信息："+uploadFile +"/n原始异常信息："+e.getLocalizedMessage();
			throw new MYPOIException(exceptonMSG,MYPOIException.MYPOIEXCEPIONTYPE_PRINTLN); 
		}
		sheet = workbook.getSheetAt(sheetFlag);
		int rows = sheet.getPhysicalNumberOfRows();
		
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		HSSFDataFormat format = workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat("@"));

		
		row =  sheet.getRow(0);
		int clumnNameFlag = ExcelDataVO.getColumnNameIndex(row);
		List<Integer> clumnValueArrayFlag = ExcelDataVO.getColumnValueIndexs(row);
		if(clumnNameFlag==-1){
			String exceptonMSG = "未在EXCEL中获取到列头信息，请检查是否存在列名为" +ExcelDataVO.COLUMNNAMETILE + "的列";
			throw new MYPOIException(exceptonMSG,MYPOIException.MYPOIEXCEPIONTYPE_PRINTLN);
		}
		for(int j=1;j<rows;j++){
			row =  sheet.getRow(j);
			clumnName = row.getCell(clumnNameFlag)==null?"":row.getCell(clumnNameFlag).toString();
			columnValueArray = new ArrayList<Object>();
			if(clumnValueArrayFlag==null||clumnValueArrayFlag.size()==0){
				excelDataVO = new ExcelDataVO(clumnName,columnValueArray);
			}else{
				for(Integer flag:clumnValueArrayFlag){
					cell = row.getCell(flag);
					if(cell!=null){
						if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
							DecimalFormat df = new DecimalFormat("#");
							columnValueArray.add(df.format(cell.getNumericCellValue()));
						}else{
							columnValueArray.add(cell.toString());
						}
					}else{
						columnValueArray.add("");
					}
				}
				excelDataVO = new ExcelDataVO(clumnName,columnValueArray);
			}
			dataStrList.add(excelDataVO);
		} 
		
		//取到数据后根据列表顺序输出
		sbResultArray = new ArrayList<StringBuffer>(clumnValueArrayFlag.size());
		for(@SuppressWarnings("unused") Integer sbflag:clumnValueArrayFlag){
			sbResultArray.add(new StringBuffer());
		}
		out:
		for(String str :paraIndexs){
			if(str==null){
				continue;
			}
			
			for(ExcelDataVO vo :dataStrList){
				if(str.equals(vo.getColumnName().trim())){
					int oflag = 0;
					for(Object obj:vo.getColumnValueArray()){
						sbResultArray.get(oflag++).append(str+"="+obj+", ");
					}
					continue out;
				}
			}
			//如果运行到这里说明没有找到对应值
			for(StringBuffer sb:sbResultArray){
				sb.append(str+"=, ");
			}
			
			
			
		}
		for(StringBuffer sb:sbResultArray){
			sb.delete(sb.length()-",".length()-1, sb.length()-1);
		}
		return sbResultArray;
	}
    /**
     * 将sbArrays字符串写入filePath文件 txt格式
     * @param sbArrays 数组类型，一个数据写一个文件
     * @param filePath
     * @throws MYPOIException 
     * @throws IOException
     */
	private static void writeFile(List<StringBuffer> sbArrays,String filePath) throws MYPOIException{
		for(StringBuffer sb: sbArrays){
			File file = new File(filePath +  Math.round(Math.random()*1000000));
			FileOutputStream fos = null;
			try {
				fos = new FileOutputStream(file);
				fos.write(sb.toString().trim().getBytes());
				fos.flush();
				System.out.println("成功输出文件："+file.getAbsolutePath());
			} catch (FileNotFoundException e) {
				throw new MYPOIException("未找到文件异常，文件名"+filePath +"/n原始异常信息："+e.getLocalizedMessage(), MYPOIException.MYPOIEXCEPIONTYPE_PRINTLN);
			} catch (IOException e){
				throw new MYPOIException("", MYPOIException.MYPOIEXCEPIONTYPE_PRINTLN);
			}
		}
		
	}
	/**
	 * 读取filePath 文件中的信息
	 * @param filePath
	 * @return
	 * @throws IOException
	 */
	private static String getStrFromFile(String filePath) throws IOException{
		BufferedReader in = null;
		in  = new BufferedReader(new FileReader(new File(filePath)));
		StringBuffer sb = new StringBuffer();
		String readLine = null;
		while((readLine=in.readLine())!=null){
			sb.append(readLine);
		}
		return sb.toString();
	}
	/**
	 * 获取配置文件中的配置参数
	 * @return
	 */
	private static PropertiesVO initConfigurations(boolean isUseDefault) {
		PropertiesVO propertiesVO = new PropertiesVO();
		if(isUseDefault){
			return propertiesVO.initDefault();
		}
		try {

			InputStream inputStream = propertiesVO.getClass().getClassLoader().getResourceAsStream("config/init.properties");
			Properties p = new Properties();
			p.load(inputStream);
			propertiesVO.initDefault();
			Method fieldSetMet;
			String fieldSetName;
			for (Field field : propertiesVO.getClass().getDeclaredFields()) {
				if (p.getProperty(field.getName().toUpperCase()) != null) {
					fieldSetName = parSetName(field.getName());
					if (checkSetMet(propertiesVO.getClass().getMethods(),
							fieldSetName)) {
						fieldSetMet = propertiesVO.getClass().getMethod(
								fieldSetName, field.getType());
						fieldSetMet.invoke(propertiesVO,
								p.getProperty(field.getName().toUpperCase()));

					}
				}
			}
		} catch (Exception e) {
			propertiesVO.initDefault();
		}

		return propertiesVO;
	}
	
	
    /**  
     * 判断是否存在某属性的 set方法  
     * @param methods  
     * @param fieldSetMet  
     * @return boolean  
     */  
    public static boolean checkSetMet(Method[] methods, String fieldSetMet) {   
        for (Method met : methods) {   
            if (fieldSetMet.equals(met.getName())) {   
                return true;   
            }   
        }   
        return false;   
    } 
    /**  
     * 拼接在某属性的 set方法  
     * @param fieldName  
     * @return String  
     */  
    public static String parSetName(String fieldName) {   
        if (null == fieldName || "".equals(fieldName)) {   
            return null;   
        }   
        return "set" + fieldName.substring(0, 1).toUpperCase()   
                + fieldName.substring(1);   
    }   

}
